SELECT name 
FROM houses;

SELECT characters.name,characters.surname 
FROM characters
JOIN studies 
ON studies.student=characters.id
JOIN houses 
ON houses.id=studies.house
WHERE houses.name='Griffindor'
UNION
SELECT characters.name,characters.surname 
FROM characters
JOIN is_head_of_house 
ON characters.id=is_head_of_house.teacher
JOIN houses 
ON houses.id=is_head_of_house.house
WHERE houses.name='Griffindor';

SELECT characters.name,characters.surname, houses.name 
FROM studies
JOIN characters 
ON studies.student=characters.id
JOIN houses 
ON houses.id=studies.house
ORDER BY houses.id;

SELECT characters.name,characters.surname 
FROM characters
JOIN is_headmaster 
ON characters.id=is_headmaster.teacher
JOIN teaches 
ON characters.id=teaches.teacher;

SELECT characters.name,characters.surname 
FROM characters
JOIN studies 
ON characters.id=studies.student
WHERE characters.OP_member='1'
UNION
SELECT characters.name,characters.surname 
FROM characters
JOIN teaches 
ON characters.id=teaches.teacher
WHERE characters.OP_member='1';

SELECT name, surname
FROM characters
JOIN teaches
ON characters.id=teaches.teacher
JOIN subjects
ON teaches.subject=subjects.id
WHERE subjects.description='Defence Against the Dark Arts';

SELECT title, name, surname, suffix, alias, description
FROM characters
JOIN breeds
ON characters.breed=breeds.id
WHERE description<>'Witch or Wizard'
AND description<>'Muggle'
AND description<>'Squib'
ORDER BY breed;

SELECT DISTINCT title, name, surname, suffix, alias
FROM characters
LEFT JOIN teaches
ON characters.id=teaches.teacher
WHERE teaches.teacher IS NULL
ORDER BY surname, name;

SELECT name, surname, description
FROM characters
JOIN teaches
ON characters.id=teaches.teacher
JOIN subjects
ON subjects.id=teaches.subject
ORDER BY description;